package repositorio;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import dados.Amostra;

import dados.Paciente;

public class RepositorioPaciente extends SentencasBD {

	public RepositorioPaciente(ConexaoBD conexao) {
		super(conexao, 1, 2, 3, 3);
		
		this.cadastro[0] = "INSERT INTO tb_paciente (id, sexo, nome, rg, cpf, data_nas, endereco," + 
						   " convenio, amostras) VALUES (?, ?, ?, ?, ?, ?, ?, ?, tp_nt_amostra())";
		this.atualizacao[0] = "UPDATE tb_paciente SET sexo = ?, nome = ?, rg = ?, cpf = ?, data_nas = ?, " +
							  "endereco = ?, convenio = ?, amostras = "; 
		this.atualizacao[1] = "UPDATE tb_paciente SET foto = ? WHERE id = ?";
		this.consulta[0] = "SELECT * FROM tb_paciente E WHERE E.id = ?";
		this.consulta[1] = "SELECT M.id, M.nome, M.cpf, M.rg, M.convenio FROM tb_paciente M ORDER BY M.nome";
		this.consulta[2] = "SELECT M.id, M.nome, M.cpf, M.rg, M.convenio FROM tb_paciente M WHERE M.nome LIKE ? ORDER BY M.nome";
		this.remocao[0] = "DELETE FROM tb_paciente WHERE id = ?";	}
	
	public void inserir(Paciente p) throws SQLException{
		if(p != null){
			Connection c = conexao.getConexao();
			PreparedStatement ps = c.prepareStatement(this.cadastro[0]);
			ps.setInt(1, p.getId());
			ps.setString(2, ""+ p.getSexo());
			ps.setString(3, p.getNome());
			ps.setLong(4, p.getRg());
			ps.setLong(5, p.getCpf());
			ps.setDate(6, p.getDataNas());
			ps.setString(7, p.getEndereco());
			ps.setString(8, p.getConvenio());
			
			ps.executeUpdate();
			ps.close();
			c.close();
			
			if(p.getFoto() != null){
				c = conexao.getConexao();
				ps = c.prepareStatement(this.atualizacao[1]);
				ps.setBytes(1, p.getFoto());
				ps.setInt(2, p.getId());
				ps.executeUpdate();
				ps.close();
				c.close();
			}
		}
		else
			throw new IllegalArgumentException();
	}
	
	public void editar(Paciente p) throws SQLException{
		String amostras = "tp_nt_amostra(";
		for(int i = 0; p.getAmostras() != null && i < p.getAmostras().size(); i++){
			Amostra ce = p.getAmostras().get(i);
			amostras += "(SELECT REF(C) FROM tb_amostra C WHERE C.id = " + ce.getId() + ")";
			if(i != p.getAmostras().size()-1)
				amostras += ",";
		}
		amostras += ")";
		
		Connection c = conexao.getConexao();
		PreparedStatement ps = c.prepareStatement(this.atualizacao[0] + amostras + " WHERE id = ?");
		ps.setString(1, ""+ p.getSexo());
		ps.setString(2, p.getNome());
		ps.setLong(3, p.getRg());
		ps.setLong(4, p.getCpf());
		ps.setDate(5, p.getDataNas());
		ps.setString(6, p.getEndereco());
		ps.setString(7, p.getConvenio());
		ps.setInt(8, p.getId());
		
		ps.executeUpdate();
		ps.close();
		c.close();
		
		if(p.getFoto() != null){
			c = conexao.getConexao();
			ps = c.prepareStatement(this.atualizacao[1]);
			ps.setBytes(1, p.getFoto());
			ps.setInt(2, p.getId());
			ps.executeUpdate();
			ps.close();
			c.close();
		}		
	}
	
	public void remover(int id) throws SQLException{
		Connection c = conexao.getConexao();
		PreparedStatement ps = c.prepareStatement(this.remocao[0]);
		ps.setInt(1, id);
		ps.executeUpdate();
		ps.close();
		c.close();
	}
	
	private List<Amostra> pegarAmostras(int id) throws SQLException {
		String sql = "SELECT DEREF(ss.COLUMN_VALUE).id, DEREF(ss.COLUMN_VALUE).integridade, " +
			"DEREF(ss.COLUMN_VALUE).data FROM TABLE(SELECT C.amostras FROM tb_paciente C WHERE C.id = ?) ss";
		PreparedStatement ps = conexao.getConexao().prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet set = ps.executeQuery();
		Amostra temp = null;
		List<Amostra> ret = new ArrayList<Amostra>();
		while(set.next()){
			temp = new Amostra(set.getInt(1), set.getString(2), set.getDate(3), null);
			ret.add(temp);
		}
		return ret;
	}
	
	public Paciente buscar(int id) throws SQLException{
		PreparedStatement ps = conexao.getConexao().prepareStatement(consulta[0]);
		ps.setInt(1, id);
		ResultSet set = ps.executeQuery();
		Paciente temp = null;
		if(set.next()){
			temp = new Paciente(set.getInt(1), set.getString(2).charAt(0), set.getString(3),
				set.getLong(4), set.getLong(5), set.getDate(6), set.getString(7), 
				set.getString(9), set.getBytes(8));
			//System.out.println(">> passou");
			temp.setAmostras(this.pegarAmostras(id));
			//System.out.println(">> passou2");
		}
		return temp;
	}

	public Iterator<Paciente> buscar(String nome) throws SQLException{
		return null;
	}
	
	public Iterator<Paciente> iterator() throws SQLException{
		PreparedStatement ps = conexao.getConexao().prepareStatement(consulta[1]);
		ResultSet set = ps.executeQuery();
		List<Paciente> all = new ArrayList<Paciente>();
		Paciente temp = null;
		while(set.next()){
			temp = new Paciente(set.getInt(1), set.getString(2), set.getLong(3), set.getLong(4), set.getString(5));
			all.add(temp);
		}
		
		return all.iterator();
	}
	
	/*
	public static void main(String[] args) {
		try {
			ConexaoBD c = new ConexaoBD("oracle.jdbc.driver.OracleDriver",
					"jdbc:oracle:thin:@itapissuma.cin.ufpe.br:1521:dbdisc", 
					"g101if685_eq02", "equipe2gdi");
			System.out.println("passou daqui 1");
			RepositorioPaciente r = new RepositorioPaciente(c);
			Paciente p = new Paciente(87879, 'M', "nomePac1", 874512, 984532, new Date(65654654), "endPac1", "convenio1");
			r.inserir(p);
			System.out.println("INSERIU");
			System.out.println("BUSCANDO");
			Paciente teste = r.buscar(87879);
			if(teste.getRg() == 874512) System.out.println("INSERCAO E BUSCA OK");
			p.adicionarAmostra(new Amostra(1214, "i", new Date(1), null));
			System.out.println("ATUALIZANDO...");
			r.editar(p);
			teste = r.buscar(87879);
			System.out.println("- Amostras do paciente atualizado:");
			for(Amostra pac : teste.getAmostras())
				System.out.println("  " + pac.getId() + "-" + pac.getIntegridade());
			
			Iterator<Paciente> it = r.iterator();
			while(it.hasNext()){
				System.out.println("ID paciente: " + it.next().getId());
			}
		} catch (ClassNotFoundException e) {
			System.out.println(">> erro1");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println(">> erro2");
			e.printStackTrace();
		}
	}*/
}
